﻿using System;
using System.Data;
using System.Configuration;
using System.Data.Common;

namespace DAL
{
    public class GenericDataAccess
    {
        //static string connectionString = @"Server=NGUYEN-4D2DD3C9\SQLEXPRESS; Database=WebpageDBTest; User=WebpageDBAdmin; Password=123456";
        static string connectionString = @"Server=MAY-02\SQLEXPRESS; Database=WebpageDBVietbao_3_full; User=WebpageDBAdmin; Password=123456";
        // static constructor 
        static GenericDataAccess()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Set the connection string of this database.
        /// </summary>
        /// <param name="newConnectionString">The input connection string</param>
        public static void SetConnectionString(string newConnectionString)
        {
            connectionString = newConnectionString;
        }

        /// <summary>
        /// Test a connection string is working or not
        /// </summary>
        /// <param name="connectionString">The input connection string</param>
        /// <returns>True if this connection string works, false if not</returns>
        public static bool TestConnectionString(string connectionString)
        {
            try
            {
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    conn.Open();
                    conn.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        // executes a command and returns the results as a DataTable object
        public static DataTable ExecuteSelectCommand(DbCommand command)
        {
            // The DataTable to be returned 
            DataTable table;
            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the data connection 
                command.Connection.Open();
                // Execute the command and save the results in a DataTable
                DbDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);

                // Close the reader 
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }
            return table;
        }

        // creates and prepares a new DbCommand object on a new connection
        public static DbCommand CreateCommand()
        {
            // Obtain the database provider name
            //string dataProviderName = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
            string dataProviderName = "System.Data.SqlClient";
            // Obtain the database connection string
            
            // Create a new data provider factory
            DbProviderFactory factory = DbProviderFactories.
            GetFactory(dataProviderName);
            // Obtain a database specific connection object
            DbConnection conn = factory.CreateConnection();
            // Set the connection string
            conn.ConnectionString = connectionString;
            // Create a database specific command object
            DbCommand comm = conn.CreateCommand();
            // Set the command type to stored procedure
            comm.CommandType = CommandType.StoredProcedure;
            // Return the initialized command object
            return comm;
        }

        // execute an update, delete, or insert command 
        // and return the number of affected rows
        public static int ExecuteNonQuery(DbCommand command)
        {
            // The number of affected rows 
            int affectedRows = -1;
            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();
                // Execute the command and get the number of affected rows
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }
            // return the number of affected rows
            return affectedRows;
        }

        // execute a scalar query
        public static int ExecuteScalarQuery(DbCommand command)
        {
            // The number of affected rows 
            int value = -1;
            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();
                // Execute the command and get the number of affected rows
                value = (int)command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }
            // return the number of affected rows
            return value;
        }
    }
}
